from datetime import datetime
from typing import Optional, List
from uuid import UUID
import asyncpg
from asyncpg import Pool
from src.models.domain.user import User
from src.models.schemas.auth import UserCreate, UserUpdate


class UserRepository:
    def __init__(self, db_pool: Pool):
        self.db_pool = db_pool
    
    async def create(self, user_data: UserCreate, password_hash: str) -> Optional[User]:
        query = """
            INSERT INTO users (email, username, password_hash, phone, role)
            VALUES ($1, $2, $3, $4, $5)
            RETURNING id, email, username, password_hash, phone, role, is_active, 
                      email_verified, created_at, updated_at, last_login_at
        """
        
        async with self.db_pool.acquire() as connection:
            try:
                row = await connection.fetchrow(
                    query,
                    user_data.email,
                    user_data.username,
                    password_hash,
                    user_data.phone,
                    'user'  # Default role
                )
                
                if row:
                    user = User()
                    user.id = row['id']
                    user.email = row['email']
                    user.username = row['username']
                    user.password_hash = row['password_hash']
                    user.phone = row['phone']
                    user.role = row['role']
                    user.is_active = row['is_active']
                    user.email_verified = row['email_verified']
                    user.created_at = row['created_at']
                    user.updated_at = row['updated_at']
                    user.last_login_at = row['last_login_at']
                    return user
                return None
            except asyncpg.UniqueViolationError:
                return None
    
    async def get_by_id(self, user_id: UUID) -> Optional[User]:
        query = """
            SELECT id, email, username, password_hash, phone, role, is_active,
                   email_verified, created_at, updated_at, last_login_at
            FROM users
            WHERE id = $1 AND is_active = true
        """
        
        async with self.db_pool.acquire() as connection:
            row = await connection.fetchrow(query, user_id)
            
            if row:
                user = User()
                user.id = row['id']
                user.email = row['email']
                user.username = row['username']
                user.password_hash = row['password_hash']
                user.phone = row['phone']
                user.role = row['role']
                user.is_active = row['is_active']
                user.email_verified = row['email_verified']
                user.created_at = row['created_at']
                user.updated_at = row['updated_at']
                user.last_login_at = row['last_login_at']
                return user
            return None
    
    async def get_by_email(self, email: str) -> Optional[User]:
        query = """
            SELECT id, email, username, password_hash, phone, role, is_active,
                   email_verified, created_at, updated_at, last_login_at
            FROM users
            WHERE email = $1 AND is_active = true
        """
        
        async with self.db_pool.acquire() as connection:
            row = await connection.fetchrow(query, email)
            
            if row:
                user = User()
                user.id = row['id']
                user.email = row['email']
                user.username = row['username']
                user.password_hash = row['password_hash']
                user.phone = row['phone']
                user.role = row['role']
                user.is_active = row['is_active']
                user.email_verified = row['email_verified']
                user.created_at = row['created_at']
                user.updated_at = row['updated_at']
                user.last_login_at = row['last_login_at']
                return user
            return None
    
    async def get_by_phone(self, phone: str) -> Optional[User]:
        query = """
            SELECT id, email, username, password_hash, phone, role, is_active,
                   email_verified, created_at, updated_at, last_login_at
            FROM users
            WHERE phone = $1 AND is_active = true
        """
        
        async with self.db_pool.acquire() as connection:
            row = await connection.fetchrow(query, phone)
            
            if row:
                user = User()
                user.id = row['id']
                user.email = row['email']
                user.username = row['username']
                user.password_hash = row['password_hash']
                user.phone = row['phone']
                user.role = row['role']
                user.is_active = row['is_active']
                user.email_verified = row['email_verified']
                user.created_at = row['created_at']
                user.updated_at = row['updated_at']
                user.last_login_at = row['last_login_at']
                return user
            return None
    
    async def get_by_username(self, username: str) -> Optional[User]:
        query = """
            SELECT id, email, username, password_hash, phone, role, is_active,
                   email_verified, created_at, updated_at, last_login_at
            FROM users
            WHERE username = $1 AND is_active = true
        """
        
        async with self.db_pool.acquire() as connection:
            row = await connection.fetchrow(query, username)
            
            if row:
                user = User()
                user.id = row['id']
                user.email = row['email']
                user.username = row['username']
                user.password_hash = row['password_hash']
                user.phone = row['phone']
                user.role = row['role']
                user.is_active = row['is_active']
                user.email_verified = row['email_verified']
                user.created_at = row['created_at']
                user.updated_at = row['updated_at']
                user.last_login_at = row['last_login_at']
                return user
            return None
    
    async def update(self, user_id: UUID, user_data: UserUpdate) -> Optional[User]:
        updates = []
        values = []
        param_count = 1
        
        if user_data.username is not None:
            updates.append(f"username = ${param_count}")
            values.append(user_data.username)
            param_count += 1
        
        if user_data.phone is not None:
            updates.append(f"phone = ${param_count}")
            values.append(user_data.phone)
            param_count += 1
        
        if not updates:
            return await self.get_by_id(user_id)
        
        updates.append(f"updated_at = ${param_count}")
        values.append(datetime.utcnow())
        param_count += 1
        
        values.append(user_id)
        
        query = f"""
            UPDATE users
            SET {', '.join(updates)}
            WHERE id = ${param_count}
            RETURNING id, email, username, password_hash, phone, role, is_active,
                      email_verified, created_at, updated_at, last_login_at
        """
        
        async with self.db_pool.acquire() as connection:
            row = await connection.fetchrow(query, *values)
            
            if row:
                user = User()
                user.id = row['id']
                user.email = row['email']
                user.username = row['username']
                user.password_hash = row['password_hash']
                user.phone = row['phone']
                user.role = row['role']
                user.is_active = row['is_active']
                user.email_verified = row['email_verified']
                user.created_at = row['created_at']
                user.updated_at = row['updated_at']
                user.last_login_at = row['last_login_at']
                return user
            return None
    
    async def update_last_login(self, user_id: UUID) -> bool:
        query = """
            UPDATE users
            SET last_login_at = $1
            WHERE id = $2
        """
        
        async with self.db_pool.acquire() as connection:
            await connection.execute(query, datetime.utcnow(), user_id)
            return True
    
    async def update_password(self, user_id: UUID, password_hash: str) -> bool:
        query = """
            UPDATE users
            SET password_hash = $1, updated_at = $2
            WHERE id = $3
        """
        
        async with self.db_pool.acquire() as connection:
            await connection.execute(query, password_hash, datetime.utcnow(), user_id)
            return True
    
    async def delete(self, user_id: UUID) -> bool:
        query = """
            UPDATE users
            SET is_active = false, updated_at = $1
            WHERE id = $2
        """
        
        async with self.db_pool.acquire() as connection:
            await connection.execute(query, datetime.utcnow(), user_id)
            return True
    
    async def exists_by_email(self, email: str) -> bool:
        query = "SELECT EXISTS(SELECT 1 FROM users WHERE email = $1)"
        
        async with self.db_pool.acquire() as connection:
            result = await connection.fetchval(query, email)
            return result
    
    async def exists_by_username(self, username: str) -> bool:
        query = "SELECT EXISTS(SELECT 1 FROM users WHERE username = $1)"
        
        async with self.db_pool.acquire() as connection:
            result = await connection.fetchval(query, username)
            return result